﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using ProgNetComponents.Classes;

namespace ProgNetComponents.Data
{
    /// <summary>
    /// Base class to read DB schema
    /// </summary>
    public abstract class SchemaChecker
    {
        private static Dictionary<string, SchemaChecker> _AllSchemas = new Dictionary<string, SchemaChecker>();
        /// <summary>
        /// Checks if column exists in schema for specified conenction string
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="table"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        public static bool ColumnExists(string connectionString, string table, string column)
        {
            bool res = false;
            if (_AllSchemas.ContainsKey(connectionString.AsString().ToUpper()))
            {
                SchemaChecker k = _AllSchemas[connectionString.AsString().ToUpper()];
                res = k.ColumnExists(table, column);
            }
            return res;
        }
        /// <summary>
        /// Checks if column exists in schema for specified conenction string
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="table"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        public static bool ColumnExists(string connectionString, DataTable table, DataColumn column)
        {
            return ColumnExists(connectionString, table.TableName, column.ColumnName);
        }
        /// <summary>
        /// Checks if record is used in specified conenction string. Only existing references are checked.
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="row"></param>
        /// <returns></returns>
        public static bool IsRecordUsed(string connectionString, DataRow row)
        {
            bool res = false;
            if (_AllSchemas.ContainsKey(connectionString.AsString().ToUpper()))
            {
                SchemaChecker k = _AllSchemas[connectionString.AsString().ToUpper()];
                res = k.IsRecordUsed(row);
            }
            return res;
        }

        private SchemaDataSet _SchemaDataSet = new SchemaDataSet();
        private DataManager _Manager;
        
        /// <summary>
        /// Creates SchemaChecker
        /// </summary>
        /// <param name="manager"></param>
        protected SchemaChecker(DataManager manager)
        {
            _Manager = manager;
            GetSchema();
        }
        /// <summary>
        /// Gets DataManager
        /// </summary>
        protected DataManager Manager
        {
            get
            {
                return _Manager;
            }
        }
        /// <summary>
        /// Gets Manager's ConnectionString
        /// </summary>
        protected string ConnectionString
        {
            get { return Manager.ConnectionString; }
        }
        /// <summary>
        /// Gets DataSet with current schema
        /// </summary>
        public SchemaDataSet SchemaDataSet
        {
            get { return _SchemaDataSet; }
        }
        /// <summary>
        /// Fills DataSet with current schema
        /// </summary>
        /// <param name="schema"></param>
        protected abstract void FillSchemaDataSet(SchemaDataSet schema);
        /// <summary>
        /// Fills SchemaDataSet if needed
        /// </summary>
        public virtual void GetSchema()
        {
            if (!_AllSchemas.ContainsKey(ConnectionString.AsString().ToUpper()))
                RefreshSchema();
            else
                this._SchemaDataSet = _AllSchemas[ConnectionString.AsString().ToUpper()].SchemaDataSet;
        }
        /// <summary>
        /// Clears SchemaDataSet, and refills it
        /// </summary>
        public virtual void RefreshSchema()
        {
            SchemaDataSet.Clear();
            FillSchemaDataSet(SchemaDataSet);
            if (!_AllSchemas.ContainsKey(ConnectionString.AsString().ToUpper()))
                _AllSchemas.Add(ConnectionString.AsString().ToUpper(), this);
        }
        /// <summary>
        /// Checks if column exists in table
        /// </summary>
        /// <param name="table"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        public virtual bool ColumnExists(string table, string column)
        {
            return SchemaDataSet.COLUMNS.RowExists("TABLE_NAME = " + table.EscapeSql() + " AND COLUMN_NAME = " + column.EscapeSql());
        }
        /// <summary>
        /// Checks if column exists in table
        /// </summary>
        /// <param name="table"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        public virtual bool ColumnExists(DataTable table, DataColumn column)
        {
            return ColumnExists(table.TableName, column.ColumnName);
        }
        /// <summary>
        /// Check if record is used. Only References are checked.
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public virtual bool IsRecordUsed(DataRow row)
        {
            string TableName = row.Table.TableName;
            DataRow[] refs = SchemaDataSet.REF_CONSTRAINTS.Select("DELETE_RULE = 'NO ACTION' AND PRIMARY_TABLE = " + TableName.EscapeSql());
            if (refs.Length > 0)
            {
                DataRow[] columns;
                List<NamedObject> parameters = new List<NamedObject>();
                foreach (DataRow r in refs)
                {
                    parameters.Clear();
                    columns = SchemaDataSet.REF_CONSTRAINTS_COLUMNS.Select("CONSTRAINT_NAME = " + r["CONSTRAINT_NAME"].AsString().EscapeSql());

                    if (columns.Length > 0)
                    {
                        string select = "SELECT count(*) FROM " + r["FOREIGN_TABLE"].AsString() + " WHERE ";
                        string where = "";
                        foreach (DataRow c in columns)
                        {
                            where = where.AppendAnd();
                            where += c["FOREIGN_COLUMN"].AsString() + " = @" + c["FOREIGN_COLUMN"].AsString();
                            parameters.Add(new NamedObject("@" + c["FOREIGN_COLUMN"].AsString(), row[c["PRIMARY_COLUMN"].AsString()]));
                        }
                        object obj = Manager.ExecuteScalar(select + where, parameters.ToArray());
                        if (obj.AsInt(0) > 0)
                        {
                            return true;
                        }
                    }
                }
            }
            return false;
        }
        /// <summary>
        /// Gets all columns from specified table
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public virtual List<string> GetTableColumns(string table)
        {
            GetSchema();
            List<string> l = new List<string>();
            DataRow[] rows = SchemaDataSet.COLUMNS.Select("TABLE_NAME = " + table.EscapeSql());
            foreach (DataRow r in rows)
            {
                l.Add(r["COLUMN_NAME"].AsString());
            }
            return l;
        }
        /// <summary>
        /// Gets all tables in current schema
        /// </summary>
        /// <returns></returns>
        public virtual List<string> GetTables()
        {
            GetSchema();
            List<string> l = new List<string>();
            DataRow[] rows = SchemaDataSet.COLUMNS.Select();
            foreach (DataRow r in rows)
            {
                l.AddIfNotExist(r["TABLE_NAME"].AsString());
            }
            return l;
        }
    }

    
}
